package com.upup.train_management.repository;

import com.upup.train_management.entity.Route;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
public interface RouteRepository extends JpaRepository<Route, Long> {
    Route findByRouteName(String routeName);

    List<Route> findByStartStationId(Long startStationId);

    List<Route> findByEndStationId(Long endStationId);

    List<Route> findByStartStationIdAndEndStationId(Long startStationId, Long endStationId);

    List<Route> findByStartStationIdInAndEndStationIdIn(List<Long> startStationIds, List<Long> endStationIds);

    @Query(value = "SELECT r.* FROM routes r " +
            "JOIN schedules s ON r.id = s.route_id " +
            "JOIN tickets t ON s.id = t.schedule_id " +
            "WHERE t.status = 'CONFIRMED' " +
            "GROUP BY r.id " +
            "ORDER BY COUNT(t.id) DESC " +
            "LIMIT :limit", nativeQuery = true)
    List<Route> findMostPopularRoutes(@Param("limit") Integer limit);
}